/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       OracleExecutor.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/11/9      feiwe                         add
 */


package com.fengwei.executor;

import cn.hutool.core.util.CharsetUtil;
import cn.hutool.crypto.symmetric.SymmetricAlgorithm;
import cn.hutool.crypto.symmetric.SymmetricCrypto;
import com.alibaba.druid.pool.DruidDataSource;
import com.fengwei.bean.DataSourcePoolManager;
import com.fengwei.bean.Page;
import com.fengwei.config.CommonProperty;
import com.fengwei.util.GsonUtil;
import com.fengwei.util.HexStringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service("oracleExecutor")
public class OracleExecutor extends AbstractDbExecutor {
    private final static Logger logger = LoggerFactory.getLogger(OracleExecutor.class);

    @Autowired
    private DataSourcePoolManager dataSourcePoolManager;

    @Autowired
    private CommonProperty commonProperty;

    @Override
    public String genUrl(String databaseType, String databaseHost, Integer databasePort, String databaseDbname) {
        //oracle 12c之前
        //String url = "jdbc:"+databaseType+":thin:@"+databaseHost+":"+databasePort+":"+databaseDbname+"";
        //oracle 12c
        String url = "jdbc:" + databaseType + ":thin:@(description=(address=(protocol=tcp)(port=" + databasePort + ")(host=" + databaseHost + "))(connect_data=" +
                "(service_name=" + databaseDbname + ")))";

        return url;
    }

    @Override
    public synchronized Connection getConnection(String dbId, String url, String userName, String passWord) throws ClassNotFoundException, SQLException {
        DruidDataSource druidDataSource = dataSourcePoolManager.getDataSourcePoolManagerMap().get(dbId);
        if (null == druidDataSource) {
            druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(url);
            druidDataSource.setDriverClassName(DataSourcePoolManager.ORACLE_DRIVER);
            druidDataSource.setUsername(userName);
            SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.AES, HexStringUtil.hexStringToBytes(commonProperty.getDatabaseAesKey()));
            String passWordDec = aes.decryptStr(passWord, CharsetUtil.CHARSET_UTF_8);
            druidDataSource.setPassword(passWordDec);
            druidDataSource.setInitialSize(commonProperty.getDatabaseInitialSize());
            druidDataSource.setMaxActive(commonProperty.getDatabaseMaxActive());
            druidDataSource.setMinIdle(commonProperty.getDatabaseMinIdle());
            druidDataSource.setMaxWait(2000);
            druidDataSource.setPoolPreparedStatements(true);
            druidDataSource.setMaxOpenPreparedStatements(20);
            druidDataSource.setValidationQuery("SELECT 1 FROM DUAL");
            druidDataSource.setTestWhileIdle(false);
            druidDataSource.setTestOnBorrow(false);
            druidDataSource.setTestOnReturn(true);
            druidDataSource.setFilters("com.fengwei.dao.MySlf4jLogFilter,stat");
            druidDataSource.init();
            dataSourcePoolManager.getDataSourcePoolManagerMap().put(dbId, druidDataSource);
        }
        Connection connection = druidDataSource.getConnection();
        return connection;
    }

    @Override
    public Page<Map<String, Object>> executeSelectSql(Page<Map<String, Object>> page, Connection connect, String sql) throws Exception {
        int pageNo = page.getPageNo();
        int pageSize = page.getPageSize();
        int limitFrom = (pageNo - 1) * pageSize;
        sql = sql.trim();

        String finalSql =
                "select * from (select rownum rn, t1.* from (" + sql + ") t1 where rownum<=" + (limitFrom + pageSize) + ") where rn > " + limitFrom;
        logger.debug("fianlSql:" + finalSql);

        List<Map<String, Object>> list = super.queryForList(connect, finalSql);
        logger.debug("GsonUtil.toJson(list,false):" + GsonUtil.toJson(list, false));
        int rowCount = super.executeQueryForCount(connect, sql);
        logger.debug("rowCount:" + rowCount);
        List<Map<String, Object>> columns = super.executeSqlForColumns(connect, sql);
        Map columnTypeMap = new HashMap();
        List<Map<String, Object>> tempList = new ArrayList<>();
        for (Map<String, Object> map : columns) {
            columnTypeMap.put(map.get("column_name"), ((String) map.get("data_type")).toLowerCase());

            Map<String, Object> map2 = new HashMap<>();
            map2.put("field", map.get("column_name"));
            map2.put("title", map.get("column_name"));
            map2.put("sortable", Boolean.valueOf(true));
            if (map.get("data_type").equals("DATETIME")) {
                map2.put("editor", "datetimebox");
            } else if ((map.get("data_type").equals("INT")) || (map.get("data_type").equals("SMALLINT")) || (map.get("data_type").equals("TINYINT"))) {
                map2.put("editor", "numberbox");
            } else if (map.get("data_type").equals("DOUBLE")) {
                map2.put("editor", "numberbox");
            } else if ((!map.get("data_type").equals("BLOB")) && (!map.get("data_type").equals("CLOB"))) {
                if ((!map.get("data_type").equals("VARBINARY")) && (!map.get("data_type").equals("BINARY"))) {
                    map2.put("editor", "text");
                }
            }
            tempList.add(map2);
        }

        String primaryKey = "";
        String tableName = "";
        String temp = "";

        page.setTotalCount(rowCount);
        page.setResult(list);
        page.setColumns(tempList);
        page.setColumnType(columnTypeMap);
        //page.setPrimaryKey(primaryKey);
        page.setTableName(tableName);

        return page;
    }

    @Override
    public List getAllTables(Connection connection, String userName) throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String finalSql = " select table_name from all_tables where owner='" + userName.toUpperCase() + "' order by table_name";
        List tableList = new ArrayList<>();
        try {
            pstmt = connection.prepareStatement(finalSql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                tableList.add(rs.getString(1));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw e;
        } finally {
            rs.close();
            pstmt.close();
        }
        return tableList;
    }

    @Override
    public String ddl(Connection connection, String tableName, String userName) throws Exception {
        String finalSql = "select dbms_metadata.get_ddl('TABLE','" + tableName.toUpperCase() + "','" + userName.toUpperCase() + "') from dual";
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = connection.prepareStatement(finalSql);
            rs = pstmt.executeQuery();
            rs.next();
            String createTable = rs.getString(1);
            return createTable;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw e;
        } finally {
            rs.close();
            pstmt.close();
        }
    }

    @Override
    public Map getPrimaryKey(String tableName, Connection connection) throws Exception {
        String finalSql = "select  COLUMN_NAME from user_cons_columns  where constraint_name= (select  constraint_name  from user_constraints  where table_name = '" + tableName + "' and constraint_type ='P')";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map mapKey = new HashMap();
        try {
            pstmt = connection.prepareStatement(finalSql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String columnName = rs.getString(1);
                mapKey.put(columnName, "1");
            }
            return mapKey;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw e;
        } finally {
            rs.close();
            pstmt.close();
        }
    }
}
